Week 3

April 20, 2023

1. TimeScaleDB PostgreSQL querries run on Grafana. Includes JSON parsing via CAST method

   SELECT measurement, CAST(measurement->>'Bat' AS float) AS bat, CAST(measurement->>'Distance' AS int) AS distance  FROM api_measurement where device_id != '2CF7F12032301C43' limit 50

2. Method to clean the stitched image OCR distance value outputs

   1. Remove the first 40 rows from the dataframe of each video to skip the initial info screen phase of the video. (via Python)
   2. Remove outliers by binning the distance values together with a bin size of 5 and then dropping any bins which gets less than 5 items. (via SQL)

   select * from distance_pd where frame_id in (
      select frame_id from (
         select frame_id, (CAST(distance as INTEGER)/5)*5 as distance_bucket_1 from distance_pd where distance_bucket_1 in (
            select distance_bucket_2 from (
               select distance_bucket_2, range_count from (
                  select (CAST(distance as INTEGER)/5)*5 as distance_bucket_2, count(*) as range_count from distance_pd group by distance_bucket_2
               ) where range_count < 10
            )
         )
      )
   )
   3. Interpolate the blank distance fields (via Python)

April 19, 2023

1. Things to find out in stage measurements
   1. Any sensor message modifications done on Dragino messages on Senet LoRaWAN network
   2. How additional sensors can be connected to LDDS75, LSN50v2-D20 and how the payload will look like.
   3. lis3dh inclinometer to get the angle and how to fuse distance values + the angle to create accurate vertical distance measurments
   4. See if there is an intensity reduction due to 30 degree angle.
2. Explore Grafana and TSDB integration
3. For AI3, we can explore inferring device type based on the metadata like Eui, mac_id etc.
   1. Sudhir: Not quite. The idea is to have a large dictionary for device types and payload formats It may have an entry LSN50, {.....} Then if you get LSN50v2 as the device type which not in the dictionary, then you find the closest matching and use the to figure out the payload format.